﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebService.Connect
{
    public class Database
    {
        DataSet Dataset = new DataSet();
        private SqlConnection _Connection = new SqlConnection();

        /// <summary>
        /// Connect
        /// </summary>
        /// <returns></returns>
        public SqlConnection Connect()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
            return new SqlConnection(connectionString);
        }

        public DataSet GetDataset(string StoredProcedure)
        {
            Dataset.Clear();
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(StoredProcedure, _Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(Dataset);
            _Connection.Close();
            return Dataset;
        }

        /// <summary>
        /// GetDataSetWithParameter
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="sqlParameter"></param>
        /// <returns></returns>
        public DataSet GetDataSetWithParameter(String StoredProcedure, SqlParameter[] sqlParameter)
        {
            Dataset.Clear();
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(StoredProcedure, _Connection);
            cmd.Parameters.AddRange(sqlParameter);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(Dataset);
            _Connection.Close();
            return Dataset;

        }

        /// <summary>
        /// ExcuteQueryWithParamater
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="sqlParameter"></param>
        /// <returns></returns>
        public int ExcuteQueryWithParamater(String storedProcedure, SqlParameter[] sqlParameter)
        {
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(storedProcedure, _Connection);
            cmd.Parameters.AddRange(sqlParameter);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter paramReturnValue = new SqlParameter();
            paramReturnValue.ParameterName = "@RETURN_VALUE";
            paramReturnValue.SqlDbType = SqlDbType.Int;
            paramReturnValue.SourceColumn = null;
            paramReturnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(paramReturnValue);
            cmd.ExecuteNonQuery();
            int result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
            _Connection.Close();
            return result;
        }

        /// <summary>
        /// ExecuteQuery
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <returns></returns>
        public int ExecuteQuery(string storedProcedure)
        {
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(storedProcedure, _Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter paramReturnValue = new SqlParameter();
            paramReturnValue.ParameterName = "@RETURN_VALUE";
            paramReturnValue.SqlDbType = SqlDbType.Int;
            paramReturnValue.SourceColumn = null;
            paramReturnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(paramReturnValue);
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["@RETURN_VALUE"].Value;
        }

        /// <summary>
        /// ExecuteString
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="ListParameter"></param>
        /// <returns></returns>
        public string ExecuteString(string StoredProcedure, SqlParameter[] ListParameter)
        {
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(StoredProcedure, _Connection);
            cmd.Parameters.AddRange(ListParameter);
            cmd.CommandType = CommandType.StoredProcedure;
            string result = "";
            object OB = cmd.ExecuteScalar();
            if (OB != null)
                result = OB.ToString();
            else result = "0";
            _Connection.Close();
            return result;
        }

        /// <summary>
        /// ExecuteString
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="ListParameter"></param>
        /// <returns></returns>
        public string ExecuteString(string StoredProcedure)
        {
            _Connection = Connect();
            _Connection.Open();
            SqlCommand cmd = new SqlCommand(StoredProcedure, _Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            string result = "";
            object OB = cmd.ExecuteScalar();
            if (OB != null)
                result = OB.ToString();
            else result = "0";
            _Connection.Close();
            return result;
        }
    }
}